/**
 * 为坐骑表添加价格字段的迁移脚本
 * 功能：为现有的mount_base表添加price字段
 */

const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs'
};

class MountPriceMigration {
    constructor() {
        this.connection = null;
    }

    // 建立数据库连接
    async connect() {
        try {
            this.connection = await mysql.createConnection(dbConfig);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    // 关闭数据库连接
    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    // 检查price字段是否已存在
    async checkPriceFieldExists() {
        try {
            console.log('🔍 检查price字段是否已存在...');

            const [rows] = await this.connection.execute(`
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = 'nextjs' 
                AND TABLE_NAME = 'mount_base' 
                AND COLUMN_NAME = 'price'
            `);

            const exists = rows.length > 0;
            console.log(`📊 price字段${exists ? '已存在' : '不存在'}`);
            return exists;
        } catch (error) {
            console.error('❌ 检查price字段失败:', error.message);
            throw error;
        }
    }

    // 添加price字段
    async addPriceField() {
        try {
            console.log('📋 开始添加price字段...');

            const alterSQL = `
                ALTER TABLE mount_base 
                ADD COLUMN price INT DEFAULT 1980 COMMENT '坐骑价格' 
                AFTER type
            `;

            await this.connection.execute(alterSQL);
            console.log('✅ price字段添加成功');
        } catch (error) {
            console.error('❌ 添加price字段失败:', error.message);
            throw error;
        }
    }

    // 更新现有数据的price字段
    async updateExistingPrices() {
        try {
            console.log('🔄 更新现有坐骑的价格字段...');

            const updateSQL = `
                UPDATE mount_base 
                SET price = 1980 
                WHERE price IS NULL OR price = 0
            `;

            const [result] = await this.connection.execute(updateSQL);
            console.log(`✅ 更新了 ${result.affectedRows} 条坐骑数据的价格`);
        } catch (error) {
            console.error('❌ 更新价格字段失败:', error.message);
            throw error;
        }
    }

    // 验证迁移结果
    async verifyMigration() {
        try {
            console.log('🔍 验证迁移结果...');

            const [rows] = await this.connection.execute(`
                SELECT id, name, type, price 
                FROM mount_base 
                ORDER BY id 
                LIMIT 5
            `);

            console.log('📋 前5条坐骑数据验证:');
            rows.forEach(row => {
                console.log(`   ID: ${row.id}, 名称: ${row.name}, 类型: ${row.type}, 价格: ${row.price}`);
            });

            // 检查是否有价格为NULL的记录
            const [nullRows] = await this.connection.execute(`
                SELECT COUNT(*) as count 
                FROM mount_base 
                WHERE price IS NULL
            `);

            if (nullRows[0].count > 0) {
                console.log(`⚠️ 发现 ${nullRows[0].count} 条价格为NULL的记录`);
            } else {
                console.log('✅ 所有坐骑都有有效的价格');
            }

        } catch (error) {
            console.error('❌ 验证迁移结果失败:', error.message);
            throw error;
        }
    }

    // 运行完整的迁移流程
    async runMigration() {
        console.log('🚀 开始坐骑价格字段迁移...\n');

        try {
            // 1. 连接数据库
            await this.connect();

            // 2. 检查price字段是否已存在
            const fieldExists = await this.checkPriceFieldExists();

            if (fieldExists) {
                console.log('ℹ️ price字段已存在，跳过添加步骤');
            } else {
                // 3. 添加price字段
                await this.addPriceField();
            }

            // 4. 更新现有数据的价格
            await this.updateExistingPrices();

            // 5. 验证迁移结果
            await this.verifyMigration();

            console.log('\n🎉 坐骑价格字段迁移完成！');

        } catch (error) {
            console.error('\n💥 迁移过程中发生错误:', error.message);
            throw error;
        } finally {
            // 6. 关闭连接
            await this.disconnect();
        }
    }
}

// 运行迁移
async function main() {
    console.log('🎯 坐骑价格字段迁移脚本');
    console.log('   功能：为mount_base表添加price字段并设置默认值\n');

    const migration = new MountPriceMigration();
    await migration.runMigration();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 迁移脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = MountPriceMigration;
